﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class D_Dangky_Dichvu : AccessData
    {
        //-----Hàm lấy ra hết------        
        public DataTable GetAll()
        {
            DataTable dt = new DataTable();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Dangky_Dichvu_Get_List";
            sqlCmd.Parameters.Clear();
            sqlCmd.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
            adapter.Fill(dt);
            sqlCmd.Connection.Close();
            return dt;
        }

        //-----Hàm lấy theo PK Dangky_DichvuID------
        public M_Dangky_Dichvu GetbyPK_Dangky_DichvuID(int Dangky_DichvuID)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Dangky_Dichvu_GetByDangkyDichvuId";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@Dangky_DichvuID", Dangky_DichvuID);
            sqlCmd.Connection.Open();

            using (SqlDataReader dr = sqlCmd.ExecuteReader())
            {
                M_Dangky_Dichvu M_Dangky_Dichvu = null;
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        M_Dangky_Dichvu = new M_Dangky_Dichvu();

                        M_Dangky_Dichvu.Dangky_DichvuID = Convert.ToInt32(dr["Dangky_DichvuID"]);
                        M_Dangky_Dichvu.DangkyID = Convert.ToInt32(dr["DangkyID"]);
                        M_Dangky_Dichvu.DichvuID = Convert.ToInt32(dr["DichvuID"]);
                        if (dr["Gia"] != DBNull.Value)
                            M_Dangky_Dichvu.Gia = Convert.ToDecimal(dr["Gia"]);
                        if (dr["Ngay"] != DBNull.Value)
                            M_Dangky_Dichvu.Ngay = Convert.ToDateTime(dr["Ngay"]);
                        if (dr["Soluong"] != DBNull.Value)
                            M_Dangky_Dichvu.Soluong = Convert.ToInt32(dr["Soluong"]);                        
                    }
                }
                dr.Close();
                sqlCmd.Connection.Close();
                return M_Dangky_Dichvu;
            }
        }

        //-----Hàm lấy theo FK------        
        public DataTable GetByFK_DangkyID(int DangkyID)
        {
            DataTable dt = new DataTable();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Dangky_Dichvu_GetByDangkyId";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@DangkyID", DangkyID);
            sqlCmd.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
            adapter.Fill(dt);
            sqlCmd.Connection.Close();
            return dt;
        }
        public DataTable GetByFK_DichvuID(int DichvuID)
        {
            DataTable dt = new DataTable();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Dangky_Dichvu_GetByDichvuId";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@DichvuID", DichvuID);
            sqlCmd.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
            adapter.Fill(dt);
            sqlCmd.Connection.Close();
            return dt;
        }

        //-----Hàm Insert------
        public void Insert(M_Dangky_Dichvu M_Dangky_Dichvu)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Dangky_Dichvu_Insert";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.Add(new SqlParameter("@Dangky_DichvuID", SqlDbType.Int)).Value = M_Dangky_Dichvu.Dangky_DichvuID;
            sqlCmd.Parameters.Add(new SqlParameter("@DangkyID", SqlDbType.Int)).Value = M_Dangky_Dichvu.DangkyID;
            sqlCmd.Parameters.Add(new SqlParameter("@DichvuID", SqlDbType.Int)).Value = M_Dangky_Dichvu.DichvuID;
            sqlCmd.Parameters.Add(new SqlParameter("@Gia", SqlDbType.Decimal)).Value = M_Dangky_Dichvu.Gia;
            sqlCmd.Parameters.Add(new SqlParameter("@Ngay", SqlDbType.DateTime)).Value = M_Dangky_Dichvu.Ngay;
            sqlCmd.Parameters.Add(new SqlParameter("@Soluong", SqlDbType.Int)).Value = M_Dangky_Dichvu.Soluong;            

            sqlCmd.Connection.Open();
            sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();
        }

        //-----Hàm Update------
        public bool Update(M_Dangky_Dichvu M_Dangky_Dichvu)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Dangky_Dichvu_Update";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.Add(new SqlParameter("@Dangky_DichvuID", SqlDbType.Int)).Value = M_Dangky_Dichvu.Dangky_DichvuID;
            sqlCmd.Parameters.Add(new SqlParameter("@DangkyID", SqlDbType.Int)).Value = M_Dangky_Dichvu.DangkyID;
            sqlCmd.Parameters.Add(new SqlParameter("@DichvuID", SqlDbType.Int)).Value = M_Dangky_Dichvu.DichvuID;
            sqlCmd.Parameters.Add(new SqlParameter("@Gia", SqlDbType.Decimal)).Value = M_Dangky_Dichvu.Gia;
            sqlCmd.Parameters.Add(new SqlParameter("@Ngay", SqlDbType.DateTime)).Value = M_Dangky_Dichvu.Ngay;
            sqlCmd.Parameters.Add(new SqlParameter("@Soluong", SqlDbType.Int)).Value = M_Dangky_Dichvu.Soluong;  

            sqlCmd.Connection.Open();
            int ketqua = sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();
            return (ketqua > 0);
        }

        //-----Hàm xóa------
        public bool Delete(int Dangky_DichvuID)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Dangky_Dichvu_Delete";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.Add(new SqlParameter("@Dangky_DichvuID", Dangky_DichvuID));

            sqlCmd.Connection.Open();
            int ketqua = sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();
            return (ketqua > 0);
        }
    }
}
